package com.yunhaikeji.core.poi;

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

/**
 * Excel 简单封装
 * @author bean
 *
 */
public class Excel {
	
	private Workbook workbook;
	private Sheet sheet;
	
	private Font font;
	private CellStyle style;
	
	private Map<String, CellStyle> styleMap = new HashMap<>();
	
	private Excel(Workbook workbook){
		this.workbook = workbook;
	}
	
	/**
	 * 使用静态工厂方法，语义更明显
	 * @return
	 */
	public static Excel get03Excel(){
		return new Excel(new HSSFWorkbook());
	}
	
	/**
	 * 使用静态工厂方法，语义更明显
	 * @return
	 */
	public static Excel get07Excel(){
		return new Excel(new XSSFWorkbook());
	}

	public static Excel readExcel(InputStream in) {
		try {
			return new Excel(WorkbookFactory.create(in));
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Excel createSheet(String name){
		this.sheet = workbook.createSheet(name);
		return this;
	}
	
	public Sheet getSheet(){
		return sheet;
	}

	public Sheet getSheet(int index){
		return workbook.getSheetAt(index);
	}

	public void switchSheet(int index){
		this.sheet = workbook.getSheetAt(index);
	}

	public int getRowCount(){
		return this.sheet.getLastRowNum();
	}
	
	public String getValue(int row, int col){
		Row r = sheet.getRow(row);
		if(r != null){
			Cell cell = r.getCell(col);
			if(cell != null){
				cell.setCellType(CellType.STRING);
				return cell.getStringCellValue();
			}
		}
		return null;
	}

	public int getMaxCol() {
		return sheet.getRow(0).getPhysicalNumberOfCells();
	}

	public int getMaxRow() {
		return sheet.getLastRowNum();
	}
	
	/**
	 * 
	 * @param row 0 based
	 * @param col 0 based
	 * @param value
	 */
	public void setValue( int row, int col, String value){
		setValue(row, col, value, null, (short)0);
	}
	
	public void setValue(int row, int col, String value, int height){
		setValue(row, col, value, null, height);
	}
	
	public void setValue(int row, int col, String value, CellStyle style){
		setValue(row, col, value, style, (short)0);
	}
	
	public void setValue(int row, int col, String value, CellStyle style, int height){
		Row r = sheet.getRow((short)row);
		if(r == null){
			r = sheet.createRow((short)row);
		}
		if(height != 0){
			r.setHeightInPoints(height);
		}
		Cell cell = r.createCell(col);
		cell.setCellValue(value);
		if(style != null){
			cell.setCellStyle(style);
		}
		sheet.autoSizeColumn(col);
	}
	public void setValue(int row, int col, String value, CellStyle style, int width , int height){
		Row r = sheet.getRow((short)row);
		if(r == null){
			r = sheet.createRow((short)row);
		}
		if(height != 0){
			r.setHeightInPoints(height);
		}
		Cell cell = r.createCell(col);
		cell.setCellValue(value);
		if(style != null){
			cell.setCellStyle(style);
		}
		sheet.setColumnWidth(col, width);
	}
	
	public void setLink(int row, int col, String url, CellStyle style, int height){
		if(style == null){
			style = getDefaultLinkStyle(null);
		}
		CreationHelper helper = workbook.getCreationHelper();
		Hyperlink link = helper.createHyperlink(HyperlinkType.URL);
		link.setAddress(url);
		Row r = sheet.getRow((short)row);
		if(r == null){
			r = sheet.createRow((short)row);
		}
		if(height != 0){
			r.setHeightInPoints(height);
		}
		Cell cell = r.createCell(col);
		cell.setCellValue(url);
		cell.setHyperlink(link);
		cell.setCellStyle(style);
		sheet.autoSizeColumn(col);
	}
	
	public void merge(int row1, int col1, int row2, int col2){
		sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2));
	}
	
	public Font getFont(){
		if(font == null){
			font = workbook.createFont();
		}
		return font;
	}
	
	/**************************** style method *******************************/
	
	public CellStyle getCellStyle(){
		if(style == null){
			style = createCellStyle(); 
		}
		return style;
	}
	
	public CellStyle createCellStyle(){
		return workbook.createCellStyle();
	}
	
	public synchronized CellStyle getCenterStyle(){
		if(!styleMap.containsKey("center")){
			CellStyle style = createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			styleMap.put("center", style);
		}
		return styleMap.get("center");
	}
	
	public synchronized CellStyle getLeftStyle(){
		if(!styleMap.containsKey("left")){
			CellStyle style = createCellStyle();
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setAlignment(HorizontalAlignment.LEFT);
			styleMap.put("left", style);
		}
		return styleMap.get("left");
	}
	
	public synchronized CellStyle getRightStyle(){
		if(!styleMap.containsKey("right")){
			CellStyle style = createCellStyle();
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setAlignment(HorizontalAlignment.RIGHT);
			styleMap.put("right", style);
		}
		return styleMap.get("right");
	}
	
	public CellStyle getDateFormatStyle(CellStyle style, String format){
		if(style == null)
			style = getCellStyle();
		CreationHelper helper = workbook.getCreationHelper();
		style.setDataFormat(helper.createDataFormat().getFormat(format));
		return style;
	}
	
	public CellStyle getFontStyle(CellStyle style, int fontSize, boolean bold, IndexedColors color){
		Font f = getFont();
		if(style == null){
			style = getCellStyle();
		}
		//f.setBold(bold);
		f.setFontHeightInPoints((short)fontSize);
		f.setColor((short)color.getIndex());
		style.setFont(f);
		return style;
	}
	
	public CellStyle getDefaultLinkStyle(CellStyle style){
		if(style == null){
			style = getCenterStyle();
			Font f = workbook.createFont();
		    f.setUnderline(Font.U_SINGLE);
		    f.setColor(IndexedColors.BLUE.getIndex());
		    style.setFont(f);
		}
		return style;
	}
	
	public void write(OutputStream out) throws IOException{
		workbook.write(out);
	}


}
